﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Reflection;
using ZebraCoreLib.Framework;
using System.Data.SqlClient;
using System.Threading;

namespace ZebraCoreLib.DBHelper
{
    public class SQLBuilder
    {
        public static object lockKey = new object();
        string selectTemplate = "Select * from {0} where 1=1 {1}";
        string selectTemplatePage = "Select * from (select ROW_NUMBER() OVER (order by {0} {1}) as RowNumber,* from {2} where 1=1 {3} ) t where 1=1 and RowNumber between  {4} and {5}";
        string selectTemplatePageCount = "Select count(*) from {0} where 1=1 {1}";
        string whereTemplate = " {0} = {1}{2}{1} ";
        string insertTemplate = "Insert into {0}({1}) Values({2})";
        string updateTemplate = "Update {0} Set {1} Where {2}";
        string deleteTemplate = "Delete from {0} Where {1}";

        public static string GetAutoID()
        {
            return GetAutoID("");
        }
        public static string GetAutoID(string prefix)
        {
            //mutex.WaitOne();
            lock (lockKey)
            {
                DateTime time1 = Convert.ToDateTime("2011-01-01 00:00:00");
                TimeSpan span = (DateTime.Now - time1);
                string id = prefix + span.Ticks.ToString();
                Thread.Sleep(15);
               
                return id;
            }
        }

       
        private string Format(string input, params object[] args)
        {
            return string.Format(input, args);
        }

        public string BuildSelectCommandText(object BizObject,string parameters)
        {
            
            Type BizObjectType = BizObject.GetType();
            string tableName = "";
            var class_attributes = BizObjectType.GetCustomAttributes(typeof(ZebraBizObjectAttribute), true);
            if (class_attributes != null)
            {
                tableName = (class_attributes[0] as ZebraBizObjectAttribute).TableName;
            }
            if (!parameters.ToLower().Trim().StartsWith("and"))
                parameters = "and " + parameters;
            return Format(selectTemplate, tableName, parameters);
        }
        public List<string> BuildSelectCommandTextWithPage(object BizObject, string parameters,int pageIndex,int pageSize)
        {

            Type BizObjectType = BizObject.GetType();
            string tableName = "";
            var class_attributes = BizObjectType.GetCustomAttributes(typeof(ZebraBizObjectAttribute), true);
            if (class_attributes != null)
            {
                tableName = (class_attributes[0] as ZebraBizObjectAttribute).TableName;
            }
            if (!parameters.ToLower().Trim().StartsWith("and"))
                parameters = "and " + parameters;

                   var properties= BizServiceHelper.GetZebraFieldProperties(BizObject);
            string sortField="";
            string sortMethod="asc";
            foreach (var p in properties)
            {
                var attr = BizServiceHelper.GetZebraFieldAttribute(p);
                if (attr.SortOrder)
                    sortField = string.IsNullOrEmpty(attr.DataField) ? p.Name : attr.DataField;
                if (attr.SortByDesc)
                    sortMethod = "desc";

            }
            //selectTemplatePage = "Select * from (select ROW_NUMBER() OVER (order by {0} {1}) as RowNumber,* from {2} where 1=1 {3} ) t where 1=1 and RowNumber between  {4} and {5}";


            List<string> selectCommandText = new List<string>();
            selectCommandText.Add(Format(selectTemplatePage, sortField, sortMethod, tableName, parameters, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize));
            selectCommandText.Add(Format(selectTemplatePageCount, tableName, parameters));

            return selectCommandText;
        }


        public void BuildInsertCommand(object BizObject, DbCommand command)
        {
            //SqlCommand command=new SqlCommand();
            //(1)构建Insert语句
            Type BizObjectType = BizObject.GetType();
            string tableName = "";
            var class_attributes = BizObjectType.GetCustomAttributes(typeof(ZebraBizObjectAttribute), true);
            if (class_attributes != null)
            {
                tableName = (class_attributes[0] as ZebraBizObjectAttribute).TableName;
            }


            string fields = "";
            string parametersName = "";
            var properties = BizObjectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    //如果字段的值是null，表明不是映射字段，无需加入构建字段中
                    if ((!string.IsNullOrEmpty(attr.DataField) && (attr.DataField.ToLower() == "null") || !string.IsNullOrEmpty(attr.Foreignkey)))
                    {
                        flag = false;
                    }
                    if (property.PropertyType.IsByRef)
                        flag = false;
                }

                if (flag)
                {
                    fields += "" + property.Name + ",";
                    parametersName += "@" + property.Name + ",";
                }
            }
            if (fields.EndsWith(","))
                fields = fields.Substring(0, fields.Length - 1);
            if (parametersName.EndsWith(","))
                parametersName = parametersName.Substring(0, parametersName.Length - 1);

            command.CommandText = Format(insertTemplate, tableName, fields, parametersName);

            //(2)构建参数
            properties = BizObjectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    if ((!string.IsNullOrEmpty(attr.DataField) && (attr.DataField.ToLower() == "null") || !string.IsNullOrEmpty(attr.Foreignkey)) )
                    {
                        flag = false;
                    }
                    if (property.PropertyType.IsByRef)
                        flag = false;

                }
                if (flag)
                {
                    object filed_value = property.GetValue(BizObject, null);
                    if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = DBNull.Value;
                    if (property.PropertyType == typeof(DateTime) && ((DateTime)filed_value) <= Convert.ToDateTime("1001-1-1")) filed_value = DBNull.Value;
                    //if (filed_value == null) filed_value = DBNull.Value;
                    //SqlParameter parameter = new SqlParameter(":" + property.Name, filed_value);
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "@" + property.Name;



                    parameter.Value = filed_value == null ? DBNull.Value : filed_value;
                    // if (property.PropertyType == typeof(string) && )

                    //if (attributes != null && attributes.Length > 0)
                    //{
                    //    //自动按照GUID编号
                    //    var attr = attributes[0] as ZebraFieldAttribute;
                    //    if (attr.IsAutoID)
                    //    {
                    //        filed_value = GetAutoID();
                    //        parameter.Value = filed_value; ;// DBHelper.CreateInstance().GetAutoID(tableName); // 
                    //        //filed_value = DBHelper.CreateInstance().GetAutoID(tableName);
                    //    }
                    //    //else if (attr.IsPrimaryKey && string.IsNullOrEmpty(filed_value.ToString().Trim().Replace("0", "")))
                    //    //    parameter.Value = GetAutoID();// Guid.NewGuid().ToString().Replace("-", "");
                    //}

                    //数据验证逻辑
                    command.Parameters.Add(parameter);
                }
            }
        }

      

        public void BuildDeleteCommand<T>(T entity, DbCommand command)
        {
            //SqlCommand command = new SqlCommand();
            var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            string tableName = typeof(T).Name;
            var class_attributes = typeof(T).GetCustomAttributes(typeof(ZebraBizObjectAttribute), true);
            if (class_attributes != null)
            {
                tableName = (class_attributes[0] as ZebraBizObjectAttribute).TableName;
            }

            //(2)构建条件字段
            string where = "";
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    if (attr.IsPrimaryKey)
                    {
                        where += " and " + property.Name + " = @" + property.Name;
                        object filed_value = property.GetValue(entity, null);
                        //if (filed_value.GetType() == typeof(string) && filed_value.ToString() == "") filed_value = " ";
                        if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                        DbParameter parameter = command.CreateParameter();
                        parameter.ParameterName = "@" + property.Name;
                        parameter.Value = filed_value;
                        command.Parameters.Add(parameter);
                    }
                }
            }
            if (where.StartsWith(" and "))
                where = where.Substring(" and ".Length);
            command.CommandText = Format(deleteTemplate, tableName, where);
            //return command;
        }

        public void BuildUpdateCommand<T>(T entity, DbCommand command)
        {
            var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //(1)构建Insert语句
            string tableName = typeof(T).Name;
            var class_attributes = typeof(T).GetCustomAttributes(typeof(ZebraBizObjectAttribute), true);
            if (class_attributes != null)
            {
                tableName = (class_attributes[0] as ZebraBizObjectAttribute).TableName;
            }
            string fields = "";
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    if ((!string.IsNullOrEmpty(attr.DataField) && (attr.DataField.ToLower() == "null") || !string.IsNullOrEmpty(attr.Foreignkey) || attr.IsPrimaryKey))
                    {
                        flag = false;
                    }
                    if (property.PropertyType.IsByRef)
                        flag = false;
                }

                if (flag)
                {
                    fields += "" + property.Name + " = @" + property.Name + ",";
                }
            }
            if (fields.EndsWith(","))
                fields = fields.Substring(0, fields.Length - 1);


            //(2)构建参数
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    if ((!string.IsNullOrEmpty(attr.DataField) && (attr.DataField.ToLower() == "null") || !string.IsNullOrEmpty(attr.Foreignkey) || attr.IsPrimaryKey))
                    {
                        flag = false;
                    }
                    if (property.PropertyType.IsByRef)
                        flag = false;
                }
                if (flag)
                {
                    object filed_value = property.GetValue(entity, null);
                    //if (filed_value == null) filed_value = DBNull.Value;
                    if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = DBNull.Value;
                    if (property.PropertyType == typeof(DateTime) && ((DateTime)filed_value) <= Convert.ToDateTime("1001-1-1")) filed_value = DBNull.Value;
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "@" + property.Name;
                    parameter.Value = filed_value;
                    parameter.Value = filed_value == null ? DBNull.Value : filed_value;
                    //数据验证逻辑
                    command.Parameters.Add(parameter);
                }
            }

            //(3)构建条件字段
            string where = "";
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ZebraFieldAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ZebraFieldAttribute;
                    if (attr.IsPrimaryKey)
                    {
                        where += " and " + property.Name + " = @" + property.Name;
                        //SqlParameter parameter = new SqlParameter("@" + property.Name, property.GetValue(entity, null));
                        object filed_value = property.GetValue(entity, null);
                        if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                        DbParameter parameter = command.CreateParameter();
                        parameter.ParameterName = "@" + property.Name;
                        parameter.Value = filed_value;
                        command.Parameters.Add(parameter);
                    }
                }
            }
            if (where.StartsWith(" and "))
                where = where.Substring(" and ".Length);
            command.CommandText = Format(updateTemplate, tableName, fields, where);
        }

       
    }
}
